package com.chart.common.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * excel转换工具类
 *
 * @author 罗继全
 * 2024/7/3 18:35
 */

public class ExcelUtils {


    public static List<List<String>> wrapListMapToList(List<Map<String, Object>> dataList) {
        List<List<String>> data = new ArrayList<>();//  [["1","4"],[33,666]]
        for (Map<String, Object> row : dataList) {
            List<String> rowData = new ArrayList<>();
            for (Map.Entry<String, Object> entry : row.entrySet()) {
                rowData.add(entry.getValue().toString());
            }
            data.add(rowData);
        }
        return data;
    }


    //前8行是空白
    public static Sheet convertDataListToSheets(List<Map<String, Object>> dataList) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        // Insert 8 blank rows before starting the data
        for (int i = 0; i < 8; i++) {
            sheet.createRow(i);
        }

        // Populate sheet with data starting from the ninth row
        int rowIndex = 8; // Start from the ninth row
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowIndex++);
            int cellIndex = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                Cell cell = row.createCell(cellIndex++);
                setCellValue(cell, entry.getValue());
            }
        }

        return sheet;
    }


    //前8行是空白 第九行第一列去掉不读
    public static Sheet convertDataListToSheetRemoveNineAndOne(List<Map<String, Object>> dataList) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        // Insert 8 blank rows before starting the data
        for (int i = 0; i < 8; i++) {
            sheet.createRow(i);
        }

        // Populate sheet with data starting from the ninth row
        int rowIndex = 8; // Start from the ninth row
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowIndex++);
            int cellIndex = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                Cell cell = row.createCell(cellIndex++);
                setCellValue(cell, entry.getValue());
            }
        }

        // Remove the first cell of the ninth row (index 8)
        if (sheet.getRow(8) != null && sheet.getRow(8).getCell(0) != null) {
            sheet.getRow(8).removeCell(sheet.getRow(8).getCell(0));
        }

        return sheet;
    }

    private static void setCellValue(Cell cell, Object value) {
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value instanceof java.util.Date) {
            cell.setCellValue((java.util.Date) value);
        } else {
            cell.setCellValue(value != null ? value.toString() : "");
        }
    }

    /**
     * 前8行是空白。
     * 第9行第一列不读。
     * 把第9行后面的数据依次往前移动，填充第9行
     * @param dataList
     * @return
     */
    public static Sheet convertDataListToSheetRemoveNineAndOnes(List<Map<String, Object>> dataList) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();

        // 插入8行空白行
        for (int i = 0; i < 8; i++) {
            sheet.createRow(i);
        }

        // 填充第9行（索引8）以后的数据
        int rowIndex = 8; // 从第9行开始
        for (Map<String, Object> data : dataList) {
            Row row = sheet.createRow(rowIndex++);
            int cellIndex = 0;
            for (Map.Entry<String, Object> entry : data.entrySet()) {
                Cell cell = row.createCell(cellIndex++);
                setCellValues(cell, entry.getValue());
            }
        }

        // 移除第9行的第1个单元格
        Row ninthRow = sheet.getRow(8);
        if (ninthRow != null && ninthRow.getCell(0) != null) {
            ninthRow.removeCell(ninthRow.getCell(0));
        }

        // 将第9行后的数据向前移动以填充第9行
        for (int i = 9; i < sheet.getLastRowNum() + 1; i++) {
            Row currentRow = sheet.getRow(i);
            if (currentRow != null) {
                Row previousRow = sheet.getRow(i - 1);
                if (previousRow == null) {
                    previousRow = sheet.createRow(i - 1);
                }
                for (int j = 1; j < currentRow.getLastCellNum(); j++) {
                    Cell currentCell = currentRow.getCell(j);
                    if (currentCell != null) {
                        Cell newCell = previousRow.createCell(j - 1);
                        newCell.setCellValue(currentCell.toString());
                        currentRow.removeCell(currentCell);
                    }
                }
            }
        }

        // 删除最后一行
        int lastRowNum = sheet.getLastRowNum();
        if (lastRowNum > 8) {
            sheet.removeRow(sheet.getRow(lastRowNum));
        }

        return sheet;
    }

    private static void setCellValues(Cell cell, Object value) {
        if (value instanceof String) {
            cell.setCellValue((String) value);
        } else if (value instanceof Number) {
            cell.setCellValue(((Number) value).doubleValue());
        } else if (value instanceof Boolean) {
            cell.setCellValue((Boolean) value);
        } else if (value != null) {
            cell.setCellValue(value.toString());
        }
    }






}
